-- Start of DDL Script for Package PEMEX2.PKG_SOLICITUD
-- Generated 18-abr-2012 20:19:15 from PEMEX2@XE

CREATE OR REPLACE 
PACKAGE pkg_solicitud
  IS
  procedure obtenerSolicitud(
        pcve_solicitud IN VARCHAR, resultado out sys_refcursor
        );
  PROCEDURE  insertJefeArea(
               pcve_solicitud   IN NUMBER,
               pcve_usuario     IN NUMBER
        );
  PROCEDURE  eliminarJefeArea(
               pcve_solicitud   IN NUMBER,
               pcve_usuario     IN NUMBER
        );
  PROCEDURE  eliminarSolicitud(
               pcve_solicitud   IN varchar2
        );
  procedure getArchivos(pcve_solicitud IN VARCHAR, resultado out sys_refcursor
        );
  procedure insertaArchivo(
            pcve_solicitud     IN VARCHAR2,
            pdescripcion       IN VARCHAR2,
            pcve_usuario       IN VARCHAR2,
            pRUTA_ARCHIVO      IN VARCHAR2
        );
  PROCEDURE  eliminarArchivo(
               pcve_solicitud   IN varchar2,
               pcve_archivo     IN varchar2
        );
  PROCEDURE ActualizaJefeNotificado
         ( pcve_solicitud        IN VARCHAR2,
           pcve_usuario        IN VARCHAR2

    );
  FUNCTION getJefeTurnar(pcve_solicitud VARCHAR2,pcve_usuario VARCHAR2) RETURN sys_refcursor;
  FUNCTION getObraTurnar(pcve_obra VARCHAR2,pcve_usuario VARCHAR2) RETURN sys_refcursor;
  PROCEDURE ActualizaObraNotificado
         ( pcve_obra        IN VARCHAR2,
           pcve_usuario        IN VARCHAR2

    );
  FUNCTION getObraAsignados(pcve_obra VARCHAR2) RETURN sys_refcursor;
  FUNCTION obtenerSolicitudesAll(
             presultados        IN VARCHAR default null
           , ppagina            IN VARCHAR default null
           , pcve_usuario       IN VARCHAR default null
           , pfechaInicio       IN VARCHAR default null
           , pfechaFin          IN VARCHAR default null
           , pcve_jefe_sector   IN VARCHAR default null
           , pcve_rol           IN VARCHAR default null
           , pJEFE_SECTOR_SELEC IN VARCHAR default null
           , pNO_OFICIO         IN VARCHAR default null
           , pESTATUS           IN VARCHAR default null
    ) RETURN sys_refcursor;
  procedure criteriosSolicitud(
            pfecha_oficio        IN VARCHAR2,
            pno_oficio           IN VARCHAR2,
            pcve_activo          IN VARCHAR2,
            pcve_solicitante     IN VARCHAR2,
            pcve_estatus         IN VARCHAR2,
            presultados          IN VARCHAR2,
            ppagina              IN VARCHAR2,
            cve_usuario          in varchar2,
            pROL_BUSCAR          in varchar2
            , resultado out sys_refcursor);
  procedure updateSolicitud(
            pcve_solicitud       in VARCHAR2,
            pcve_solicitud_padre IN VARCHAR2,
            pfecha_oficio        IN VARCHAR2,
            pno_oficio           IN VARCHAR2,
            pcve_activo          IN VARCHAR2,
            pcve_tipo_solicitud  IN VARCHAR2,
            pcve_solicitante     IN VARCHAR2,
            pcve_estatus         IN VARCHAR2,
            pdescripcion         IN VARCHAR2,
            pnombre_solicitante  IN VARCHAR2,
            pnumero_nota_turno   IN VARCHAR2,
            pfecha_nota          IN VARCHAR2,
            pfilePlanos          IN VARCHAR2,
            pfileDatosPresup     IN VARCHAR2
        );
  procedure insertSolicitud(
            pcve_solicitud       out VARCHAR2,
            pcve_solicitud_padre IN VARCHAR2,
            pfecha_oficio        IN VARCHAR2,
            pno_oficio           IN VARCHAR2,
            pcve_activo          IN VARCHAR2,
            pcve_tipo_solicitud  IN VARCHAR2,
            pcve_solicitante     IN VARCHAR2,
            pcve_estatus         IN VARCHAR2,
            pdescripcion         IN VARCHAR2,
            pnombre_solicitante  IN VARCHAR2,
            pnumero_nota_turno   IN VARCHAR2,
            pfecha_nota          IN VARCHAR2,
            pcve_usuario         IN VARCHAR2,
            pfilePlanos          IN VARCHAR2,
            pfileDatosPresup     IN VARCHAR2
        );
END;
/


CREATE OR REPLACE 
PACKAGE BODY pkg_solicitud
IS
procedure insertSolicitud(
            pcve_solicitud       out VARCHAR2,
            pcve_solicitud_padre IN VARCHAR2,
            pfecha_oficio        IN VARCHAR2,
            pno_oficio           IN VARCHAR2,
            pcve_activo          IN VARCHAR2,
            pcve_tipo_solicitud  IN VARCHAR2,
            pcve_solicitante     IN VARCHAR2,
            pcve_estatus         IN VARCHAR2,
            pdescripcion         IN VARCHAR2,
            pnombre_solicitante  IN VARCHAR2,
            pnumero_nota_turno   IN VARCHAR2,
            pfecha_nota          IN VARCHAR2,
            pcve_usuario         IN VARCHAR2,
            pfilePlanos          IN VARCHAR2,
            pfileDatosPresup     IN VARCHAR2
        )
        IS
            pclave_solicitud NUMBER;
        BEGIN
            SELECT seq_solicitud.NEXTVAL INTO pclave_solicitud FROM dual;

            INSERT INTO solicitud(cve_solicitud, cve_solicitud_padre, fecha_oficio,
            no_oficio, cve_activo, cve_tipo_solicitud,
            cve_solicitante, cve_estatus, descripcion,
            nombre_solicitante, numero_nota_turno,fecha_nota,cve_usuario_mtto,FILE_PLANOS,FILE_PRESUPUESTALES)
            VALUES(pclave_solicitud, pcve_solicitud_padre, pfecha_oficio,
            pno_oficio, pcve_activo, pcve_tipo_solicitud,
            pcve_solicitante, pcve_estatus, pdescripcion,
            pnombre_solicitante, pnumero_nota_turno,pfecha_nota,pcve_usuario,pfilePlanos,pfileDatosPresup);

            pcve_solicitud := pclave_solicitud;

END;

procedure updateSolicitud(
            pcve_solicitud       in VARCHAR2,
            pcve_solicitud_padre IN VARCHAR2,
            pfecha_oficio        IN VARCHAR2,
            pno_oficio           IN VARCHAR2,
            pcve_activo          IN VARCHAR2,
            pcve_tipo_solicitud  IN VARCHAR2,
            pcve_solicitante     IN VARCHAR2,
            pcve_estatus         IN VARCHAR2,
            pdescripcion         IN VARCHAR2,
            pnombre_solicitante  IN VARCHAR2,
            pnumero_nota_turno   IN VARCHAR2,
            pfecha_nota          IN VARCHAR2,
            pfilePlanos          IN VARCHAR2,
            pfileDatosPresup     IN VARCHAR2
        )
        IS
        nextStatus               varchar2(2);
        BEGIN

         nextStatus:= pcve_estatus;
         if (pcve_estatus = '-1' and LENGTH(TRIM(pfilePlanos))>0 and LENGTH(TRIM(pfileDatosPresup))>0 ) THEN-- POR SOLICITAR
            nextStatus:= 0;
         elsif (pcve_estatus = '0' and LENGTH(TRIM(pfilePlanos))>0 and LENGTH(TRIM(pfileDatosPresup))>0 ) THEN-- SOLICITADA
            nextStatus:= 2; -- a Pendiente pues ya ha sido asignada
         END IF;

          UPDATE solicitud SET
            cve_solicitud_padre = pcve_solicitud_padre,
            fecha_oficio = pfecha_oficio,
            no_oficio = pno_oficio,
            cve_activo = pcve_activo,
            cve_tipo_solicitud = pcve_tipo_solicitud,
            cve_solicitante = pcve_solicitante,
            cve_estatus = pcve_estatus,
            descripcion = pdescripcion,
            nombre_solicitante = pnombre_solicitante,
            numero_nota_turno = pnumero_nota_turno,
            fecha_ultimo_cambio = SYSDATE,
            fecha_nota = pfecha_nota,
            FILE_PLANOS = pfilePlanos,
            FILE_PRESUPUESTALES = pfileDatosPresup
            WHERE cve_solicitud = pcve_solicitud;

END;

    FUNCTION obtenerSolicitudesAll(
             presultados        IN VARCHAR default null
           , ppagina            IN VARCHAR default null
           , pcve_usuario       IN VARCHAR default null
           , pfechaInicio       IN VARCHAR default null
           , pfechaFin          IN VARCHAR default null
           , pcve_jefe_sector   IN VARCHAR default null
           , pcve_rol           IN VARCHAR default null
           , pJEFE_SECTOR_SELEC IN VARCHAR default null
           , pNO_OFICIO         IN VARCHAR default null
           , pESTATUS           IN VARCHAR default null
    ) RETURN sys_refcursor
    IS
    resultado                      sys_refcursor;
    sqltemp                        VARCHAR(4000);
    sqlJefeSector               varchar2(1000);
    BEGIN
        sqlJefeSector:=' ( SELECT CVE_SOLICITUD FROM TURNAR_JEFE_AREA WHERE CVE_USUARIO ='||pJEFE_SECTOR_SELEC||' ) ';

        sqltemp:= ' SELECT s.cve_solicitud, s.cve_solicitud_padre, to_char(s.fecha_oficio,''dd/mm/rrrr'') fecha_oficio, s.no_oficio,
        to_char(s.fecha,''dd/mm/yyyy'') fecha, ca.descripcion activo, cts.descripcion tipo_solicitud,
        cs.descripcion area, ce.descripcion estatus, s.descripcion comentario, s.nombre_solicitante, s.numero_nota_turno,
        TO_CHAR(s.fecha_registro,''dd/mm/rrrr'') fecha_registro,
        (SELECT COUNT(*) FROM solicitud_trabajos_realizar str WHERE str.cve_solicitud = s.cve_solicitud) total_obras,
        (
            SELECT  COUNT(*)
            FROM    SOLICITUD_TRABAJOS_REALIZAR R, SOLICITUD_AFECTADO T
            WHERE   R.cve_solicitud         = S.CVE_SOLICITUD
            AND     R.cve_trabajo_realizar  = T.cve_trabajo_realizar
        ) AFECTADOS,
        ROWTOCOL('' SELECT U.nombre||'''' ''''||U.apellido_p||'''' ''''||U.apellido_m NOMBRE
            FROM   CAT_USUARIO U, turnar_jefe_area B
            WHERE  U.cve_usuario = B.cve_usuario
            AND    B.CVE_SOLICITUD =''|| S.CVE_SOLICITUD) SUPERVISOR,s.FILE_PLANOS,s.FILE_PRESUPUESTALES
        FROM solicitud s, cat_activo ca, cat_tipo_solicitud cts, cat_solicitante cs, cat_estatus ce
        WHERE s.cve_activo = ca.cve_activo
        AND s.cve_tipo_solicitud = cts.cve_tipo_solicitud
        AND s.cve_solicitante = cs.cve_solicitante
        AND s.cve_estatus = ce.cve_estatus
        '; --  AND S.cve_solicitud = T.cve_solicitud   , TURNAR_JEFE_AREA T

        IF ( (pcve_jefe_sector IS NOT NULL AND LENGTH(pcve_jefe_sector) > 0) and (pcve_rol !='0' and  pcve_rol !='10' ) ) THEN -- Ctrl.Obra

            sqltemp:=   sqltemp || ' and s.cve_solicitud in (select cve_solicitud from turnar_jefe_area where cve_usuario='||pcve_jefe_sector||' ) ';
        ELSE
            sqltemp:=   sqltemp || '(+) ';
        END IF;

        IF ( (pfechaInicio IS NOT NULL AND LENGTH(pfechaInicio) > 0) and   (pfechaFin IS NOT NULL AND LENGTH(pfechaFin) > 0)   ) THEN
            sqltemp:=   sqltemp || ' AND TRUNC(S.FECHA_REGISTRO) BETWEEN TO_DATE('''||pfechaInicio||''',''dd/mm/yyyy'') AND TO_DATE('''||pfechaFin||''',''dd/mm/yyyy'') ';
        END IF;



        IF (pJEFE_SECTOR_SELEC IS NOT NULL AND LENGTH(pJEFE_SECTOR_SELEC) > 0) THEN
            sqltemp:=   sqltemp || ' AND S.cve_solicitud IN '||sqlJefeSector;
        END IF;

        IF (pNO_OFICIO is not null and length(pNO_OFICIO) > 0) THEN
            sqltemp:=   sqltemp || ' AND homotext(S.NO_OFICIO) LIKE ''%'||homotext(pNO_OFICIO)|| '%''  ';
        END IF;

        IF (pESTATUS is not null and length(pESTATUS) > 0) THEN
            sqltemp:=   sqltemp || ' AND S.cve_estatus= '||pESTATUS;
        END IF;

        sqltemp:= sqltemp || ' ORDER BY s.fecha_registro desc ';


        IF( presultados IS NOT NULL AND ppagina IS NOT NULL)THEN
              resultado := utilerias.get_numero_fila(ppagina,presultados, sqltemp);
        ELSE
              OPEN resultado FOR sqltemp;
        END IF;


    return resultado;
    END;

    procedure obtenerSolicitud(
        pcve_solicitud IN VARCHAR, resultado out sys_refcursor
        )
    IS
    BEGIN
    OPEN resultado FOR
        SELECT s.cve_solicitud, s.cve_solicitud_padre, to_char(s.fecha_oficio,'dd/mm/rrrr') fecha_oficio, s.no_oficio, s.fecha, ca.cve_activo,
        ca.descripcion activo, cts.cve_tipo_solicitud, cts.descripcion tipo_solicitud, cs.cve_solicitante,
        cs.descripcion area, ce.cve_estatus, ce.descripcion estatus, s.descripcion comentario, s.nombre_solicitante, s.numero_nota_turno,
        TO_CHAR(s.fecha_registro,'dd/mm/rrrr') fecha_registro, TO_CHAR(s.fecha_nota,'dd/mm/rrrr') fecha_nota,
rowtocol('SELECT cu.cve_usuario || ''|'' || cu.nombre||'' ''|| cu.apellido_p||'' ''||cu.apellido_m
FROM cat_usuario cu , turnar_jefe_area t  WHERE cu.cve_usuario = t.cve_usuario  AND t.cve_solicitud = ' || s.cve_solicitud ) Jefes,
        s.FILE_PLANOS,s.FILE_PRESUPUESTALES
        FROM solicitud s, cat_activo ca, cat_tipo_solicitud cts, cat_solicitante cs, cat_estatus ce
        WHERE s.cve_activo = ca.cve_activo
        AND s.cve_tipo_solicitud = cts.cve_tipo_solicitud
        AND s.cve_solicitante = cs.cve_solicitante
        AND s.cve_estatus = ce.cve_estatus
        AND s.cve_solicitud = pcve_solicitud;

    END;

    procedure criteriosSolicitud(
            pfecha_oficio        IN VARCHAR2,
            pno_oficio           IN VARCHAR2,
            pcve_activo          IN VARCHAR2,
            pcve_solicitante     IN VARCHAR2,
            pcve_estatus         IN VARCHAR2,
            presultados          IN VARCHAR2,
            ppagina              IN VARCHAR2,
            cve_usuario          in varchar2,
            pROL_BUSCAR          in varchar2
            , resultado out sys_refcursor)
    IS
    sqltemp VARCHAR(4000);
    sqlSubSolitanteCtrlObra varchar2(1000);
    BEGIN

        sqltemp:= ' SELECT s.cve_solicitud, s.cve_solicitud_padre, to_char(s.fecha_oficio,''dd/mm/rrrr'') fecha_oficio, s.no_oficio,
        s.fecha, ca.descripcion activo, cts.descripcion tipo_solicitud,
        cs.descripcion area, ce.descripcion estatus, s.descripcion comentario, s.nombre_solicitante, s.numero_nota_turno,
        TO_CHAR(s.fecha_registro,''dd/mm/rrrr'') fecha_registro, TO_CHAR(s.fecha_nota,''dd/mm/rrrr'') fecha_nota,
        (SELECT COUNT(*) FROM solicitud_trabajos_realizar str WHERE str.cve_solicitud = s.cve_solicitud) total_obras, s.CVE_USUARIO_MTTO,
        s.FILE_PLANOS,s.FILE_PRESUPUESTALES
        FROM solicitud s, cat_activo ca, cat_tipo_solicitud cts, cat_solicitante cs, cat_estatus ce
        WHERE s.cve_activo = ca.cve_activo
        AND s.cve_tipo_solicitud = cts.cve_tipo_solicitud
        AND s.cve_solicitante = cs.cve_solicitante
        AND s.cve_estatus = ce.cve_estatus ';

        IF (pfecha_oficio IS NOT NULL) THEN
            sqltemp:= sqltemp || ' and to_char(s.fecha_oficio,''dd/mm/rrrr'') = ''' || pfecha_oficio || '''';
        END IF;

        IF (pno_oficio IS NOT NULL) THEN
            sqltemp:= sqltemp || ' and upper(s.no_oficio) like upper(''' || pno_oficio || '%'')';
        END IF;

        IF (pcve_activo IS NOT NULL) THEN
            sqltemp:= sqltemp || ' and ca.cve_activo = ' || pcve_activo;
        END IF;

        IF (pcve_solicitante IS NOT NULL) THEN
            sqltemp:= sqltemp || ' and s.cve_solicitante = ' || pcve_solicitante;
        END IF;

        IF (pcve_estatus IS NOT NULL) THEN
            sqltemp:= sqltemp || ' and ce.cve_estatus = ' || pcve_estatus;
        END IF;

        IF (cve_usuario IS NOT NULL) THEN
        insert into paso (valor) values ('pROL_BUSCAR: '||pROL_BUSCAR);
            -- IF SOY CONTROL DE OBRAS DEBO VER LO DE MI USUARIO MAS LO QUE LOS SOLICITANTES HAYAN REGISTRADO, PARA ASI PODER CAMABIARLO DE ESTATUS
            IF (trim(pROL_BUSCAR) != '0') THEN -- Control de Obras
                sqlSubSolitanteCtrlObra:= ' OR S.cve_solicitud IN ( select x.cve_solicitud from solicitud x WHERE x.CVE_USUARIO_MTTO in (SELECT CVE_USUARIO FROM CAT_USUARIO WHERE CVE_ROL=11 )  ) ';
            END IF;

            -- SI EL USUARIO ES UN COORDINADOR ENTONCES DEBE VER TODO NO FILTRAR POR USUARIO
            IF (trim(pROL_BUSCAR) != '10') THEN -- COORDINADOR
                sqltemp:= sqltemp || ' and ( s.CVE_USUARIO_MTTO =''' || cve_usuario||''' or   ''' || cve_usuario||''' in (select tj.CVE_USUARIO  from turnar_jefe_area tj where tj.CVE_SOLICITUD = s.cve_tipo_solicitud  )   '||sqlSubSolitanteCtrlObra||'    )   ';
            END IF;

        END IF;
insert into paso (VALOR) values ('sqltemp: '||sqltemp);
        sqltemp:= sqltemp || ' ORDER BY s.fecha_registro DESC ';



        IF( presultados IS NOT NULL AND ppagina IS NOT NULL)THEN
              resultado := utilerias.get_numero_fila(ppagina,presultados, sqltemp);
        ELSE
              OPEN resultado FOR sqltemp;
        END IF;

    END;

    PROCEDURE  insertJefeArea(
               pcve_solicitud   IN NUMBER,
               pcve_usuario     IN NUMBER
        )
        IS
        encontrado NUMBER;
        BEGIN
            SELECT COUNT(*) INTO encontrado FROM turnar_jefe_area WHERE cve_usuario = pcve_usuario AND
            cve_solicitud = pcve_solicitud AND cve_estatus_registro = 'VIG';

            IF(encontrado = 0) THEN
                INSERT INTO turnar_jefe_area(cve_usuario,cve_solicitud)
                VALUES(pcve_usuario,pcve_solicitud);
            END IF;

     END insertJefeArea;

      PROCEDURE  eliminarJefeArea(
               pcve_solicitud   IN NUMBER,
               pcve_usuario     IN NUMBER
        )
        IS
        BEGIN
           DELETE FROM turnar_jefe_area WHERE cve_solicitud = pcve_solicitud AND cve_usuario = pcve_usuario;

     END eliminarJefeArea;

      PROCEDURE  eliminarSolicitud(
               pcve_solicitud   IN varchar2
        )
        IS
        BEGIN
            DELETE FROM afectado_envio_pp where cve_solicitud_afectado = pcve_solicitud;
            DELETE FROM documento_envio_pp where cve_solicitud_afectado = pcve_solicitud;
            DELETE FROM solicitud_afectado where cve_solicitud_afectado = pcve_solicitud;
            DELETE FROM solicitud_afectado_federal where cve_trabajo_realizar in (select cve_trabajo_realizar from solicitud_trabajos_realizar where cve_solicitud = pcve_solicitud);
            DELETE FROM solicitud_afectado_fotografia where cve_solicitud_afectado in (select cve_trabajo_realizar from solicitud_trabajos_realizar where cve_solicitud = pcve_solicitud);
            DELETE FROM solicitud_archivos where cve_solicitud = pcve_solicitud;
            DELETE FROM solicitud_seguimiento where cve_solicitud = pcve_solicitud;
            DELETE FROM solicitud_turnar where cve_trabajo_realizar in (select cve_trabajo_realizar from solicitud_trabajos_realizar where cve_solicitud = pcve_solicitud);
            DELETE FROM turnar_jefe_area WHERE cve_solicitud = pcve_solicitud;
            DELETE FROM solicitud_trabajos_realizar WHERE cve_solicitud = pcve_solicitud;
            DELETE FROM solicitud WHERE cve_solicitud = pcve_solicitud;

     END eliminarSolicitud;


procedure insertaArchivo(
            pcve_solicitud     IN VARCHAR2,
            pdescripcion       IN VARCHAR2,
            pcve_usuario       IN VARCHAR2,
            pRUTA_ARCHIVO      IN VARCHAR2
        )
        IS
        v_clave NUMBER;
        BEGIN
            SELECT seq_sol_archivos.NEXTVAL INTO v_clave FROM dual;

            insert into SOLICITUD_ARCHIVOS (CVE_ARCHIVO,CVE_SOLICITUD,DESCRIPCION,CVE_USUARIO_MTTO,RUTA_ARCHIVO)
            VALUES (v_clave,pcve_solicitud,pdescripcion,pcve_usuario,pRUTA_ARCHIVO);

            /*pcve_solicitud||'/'||v_clave||'.'||pRUTA_ARCHIVO*/
END;


   procedure getArchivos(pcve_solicitud IN VARCHAR, resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_archivo, a.cve_solicitud, a.descripcion,  to_char(a.fecha_registro,'dd/mm/yyyy') fecha_registro,
               a.fecha_ultimo_cambio, a.cve_usuario_mtto,a.RUTA_ARCHIVO
        FROM solicitud_archivos a
        where a.cve_solicitud = pcve_solicitud
        order by  a.fecha_registro desc;
    END;

      PROCEDURE  eliminarArchivo(
               pcve_solicitud   IN varchar2,
               pcve_archivo     IN varchar2
        )
        IS
        BEGIN
           DELETE FROM SOLICITUD_ARCHIVOS WHERE CVE_ARCHIVO = pcve_archivo AND CVE_SOLICITUD = pcve_solicitud;

     END;


   -----------------------------------07042010--------------------------------------------
   PROCEDURE ActualizaJefeNotificado
         ( pcve_solicitud        IN VARCHAR2,
           pcve_usuario        IN VARCHAR2

    )IS
   BEGIN
        UPDATE TURNAR_JEFE_AREA  SET NOTIFICADO='S' WHERE
        cve_solicitud = pcve_solicitud              AND
        cve_usuario  = pcve_usuario;
   END;

   FUNCTION getJefeTurnar(pcve_solicitud VARCHAR2,pcve_usuario VARCHAR2) RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN

    OPEN resultado FOR
        SELECT HOMOTEXT(B.nombre||' '||B.apellido_p||' '||B.apellido_m) NOMBRE,B.email,NVL(A.notificado,'N') NOTIFICADO FROM TURNAR_JEFE_AREA A, CAT_USUARIO B
        WHERE   A.cve_usuario   = B.cve_usuario
        AND     A.cve_solicitud = pcve_solicitud
        AND     A.cve_usuario   = pcve_usuario;

    return resultado;
    END;

   FUNCTION getObraTurnar(pcve_obra VARCHAR2,pcve_usuario VARCHAR2) RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN

    OPEN resultado FOR
        SELECT HOMOTEXT(B.nombre||' '||B.apellido_p||' '||B.apellido_m) NOMBRE,B.email,NVL(A.notificado,'N') NOTIFICADO,
        (SELECT INITCAP(X.desc_obra) FROM SOLICITUD_TRABAJOS_REALIZAR X WHERE X.cve_trabajo_realizar = pcve_obra) DESC_OBRA
        FROM SOLICITUD_TURNAR A, CAT_USUARIO B
        WHERE   A.cve_usuario   = B.cve_usuario
        AND     A.cve_trabajo_realizar = pcve_obra
        AND     A.cve_usuario   = pcve_usuario;

    return resultado;
    END;

   PROCEDURE ActualizaObraNotificado
         ( pcve_obra        IN VARCHAR2,
           pcve_usuario        IN VARCHAR2

    )IS
   BEGIN
        UPDATE SOLICITUD_TURNAR  SET NOTIFICADO='S' WHERE
        CVE_SOLICITUD_TURNAR = pcve_obra              AND
        cve_usuario  = pcve_usuario;
   END;


   FUNCTION getObraAsignados(pcve_obra VARCHAR2) RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN

    OPEN resultado FOR
        select initcap(b.nombre||' '||b.apellido_p||' '||b.apellido_m) nombre ,nvl(b.email,'khristianandrade@tabasco.gob.mx')email
        ,firstcap(C.desc_obra) desc_obra,d.no_oficio,d.NUMERO_NOTA_TURNO
        from
            SOLICITUD_TURNAR a, cat_usuario b, solicitud_trabajos_Realizar c, SOLICITUD D
        where
            a.cve_usuario               = b.cve_usuario
        and a.CVE_TRABAJO_REALIZAR      = c.cve_trabajo_realizar
        AND C.cve_solicitud             = D.cve_solicitud
        and a.cve_trabajo_realizar      = pcve_obra;

    return resultado;
    END;
END;
/


-- End of DDL Script for Package PEMEX2.PKG_SOLICITUD
